#!/usr/bin/env python
# -*- coding:utf-8 -*-
__author__ = 'walkingsky'

import sqlite3
import datetime

class StockBoardSqlite:

    def __init__(self,dbName):
        self.connect = sqlite3.connect(dbName)
        self.cur = self.connect.cursor() 
        sql = '''CREATE TABLE  IF NOT EXISTS stock_boards 
           (stock_name TEXT, 
            stock_code TEXT, 
            date DATE, 
            count INTEGER 
            );'''
        self.cur.execute(sql)
        sql = '''CREATE TABLE  IF NOT EXISTS stock_board_details 
           (
            stock_code TEXT, 
            kind TEXT,
            time TEXT, 
            done INTEGER,
            threshold  REAL
            );'''
        self.cur.execute(sql)
        self.connect.commit()    

    def run_sql(self,sql):
        '''
        执行sqlite的sql查询，直接打印结果
        sql 执行的sql 字符串
        '''
        self.cur.execute(sql)
        self.connect.commit()
        return self.cur.fetchall()
        #print(self.cur.fetchall())

    def clean_stock_time(self):
        '''
        清空stock_time 表格，没有参数
        '''
        self.cur.execute('DELETE FROM stock_date')
        self.connect.commit()

    def close(self):
        self.cur.close()

    #---------------业务逻辑-------------------
    def write_stock_time(self,data):
        '''
        写入股票数据表格
        data 为json 数据，{"name":"123","code":"123","":""}
        '''
        code = data.get('code')
        name = data.get('name')

        _time = datetime.date.today() - datetime.timedelta(days=1)
        _time_str = _time.strftime('%Y-%m-%d')

        sql = 'SELECT count,stock_name,stock_code,date FROM stock_date WHERE stock_name="' + code + '" AND stock_code="' + code + '" AND date="'+_time_str+'" ;'
        self.cur.execute(sql)
        result = self.cur.fetchall()
        if  len(result) == 0 :
            sql = 'INSERT INTO stock_date  VALUES ("' + code + '","' + code + '" ,"'+ _time_str +'",1) ;'
            self.cur.execute(sql)
        else:
            #print(result)
            count = result[0][0] + 1
            #print(count)
            sql = 'UPDATE stock_date  SET count = '+ str(count) +' WHERE stock_name="' + code + '" AND stock_code="' + code + '" ;'
            self.cur.execute(sql)
        self.connect.commit()

    def get_now(self):
        '''
        获取当前时间的标记
        '''
        return datetime.datetime.strftime(datetime.datetime.now(),"%m-%d")

    def query_stock_kind_watch(self,data):
        '''
        查询是否有 fluctuate 的记录
        data    data['kind'] 监控类型  ：‘wave’：波动范围值，‘fluctuate’：波动幅值
                data['code'] 股票代码
                data['done'] 是否已经记录 ，0：未记录，1：记录增长 -1：记录下跌
                data['threshold']  门限值
        返回：有对应记录 True；没有对应记录 False
        '''
        now = self.get_now()
        sql = "SELECT * FROM stock_fluctuate_watch WHERE kind='"+data['kind']+"' AND stock_code='"+ data['code'] + "' AND time = '"+ now +"'  AND threshold ="+str(data['threshold'])
        self.cur.execute(sql)
        res = self.cur.fetchall()
        #print(sql)
        #print(res)
        len_res = len(res)        
        if len_res == 0:
            return False
        elif (len_res >0 and res[0][3] != data['done']):
            return 2
        else:
            return True  


    def write_stock_kind_watch(self,data):
        '''
        查询是否有 fluctuate 的记录
        data    data['kind'] 监控类型  ：‘wave’：波动范围值，‘fluctuate’：波动幅值
                data['code'] 股票代码
                data['done'] 是否已经记录 ，0：未记录，1：记录增长 -1：记录下跌
                data['threshold']  门限值
        返回：有对应记录 True；没有对应记录 False
        '''
        now = self.get_now()
        if  data['update'] == True:
            sql = "UPDATE stock_fluctuate_watch SET done = "+str(data['done'])+" WHERE kind='"+data['kind']+"' AND stock_code='"+ data['code'] + "' AND time = '"+ now +"'  AND threshold ="+str(data['threshold'])
        else:
            sql = "INSERT INTO stock_fluctuate_watch VALUES ('"+ data['code'] +"','"+data['kind']+"','"+ now+ "',"+str(data['done'])+","+str(data['threshold'])+");"
        print(sql)
        self.cur.execute(sql)
        self.connect.commit()


        


        